﻿Imports System.Data.SqlServerCe
Imports Framework.Data

Namespace Security
    Friend Class DBController
        Shared Sub New()
            Try
                Dim aSelectColumns As String() = {"Id", "Name", "Description", "Type", "SubType"}
                ObjectPersister.DefineObject(GetType(Entities.SecurityGroup), "SecurityGroups", aSelectColumns)

            Catch ex As Exception
                Framework.Misc.HandleException(ex)
            End Try
        End Sub
        Private Sub New()
        End Sub

        Public Shared Function GetAll() As Generic.List(Of Entities.SecurityGroup)
            Return ObjectPersister.GetAll(Of Entities.SecurityGroup)()
        End Function
        Public Shared Function GetAllByBuddyId(ByVal buddyId As Integer) As Generic.List(Of Entities.SecurityGroup)
            Dim strClause As String = "Id IN (SELECT SecurityGroupId FROM securitygroupsmembers WHERE BuddyId=@BuddyId)"
            Dim sqlPar1 As New SqlCeParameter("@BuddyId", buddyId)
            Return ObjectPersister.GetAll(Of Entities.SecurityGroup)(strClause, sqlPar1)
        End Function

        Public Shared Function GetById(ByVal id As Integer) As Entities.SecurityGroup
            Return ObjectPersister.GetOne(Of Entities.SecurityGroup)(String.Format("Id={0}", id))
        End Function
        Public Shared Function GetByTypeAndSubType(ByVal type As Entities.SecurityGroupType, ByVal subType As Entities.SecurityGroupSubType) As Entities.SecurityGroup
            Dim strClause As String = "Type=@Type AND SubType=@SubType"
            Dim sqlPar1 As New SqlCeParameter("@Type", type)
            Dim sqlPar2 As New SqlCeParameter("@SubType", subType)
            Return ObjectPersister.GetOne(Of Entities.SecurityGroup)(strClause, sqlPar1, sqlPar2)
        End Function
        Public Shared Sub RemoveAllMembers(ByVal securityGroupId As Integer, ByVal transactionScope As TransactionScope)
            Dim strSql As String = "DELETE FROM SecurityGroupsMembers WHERE SecurityGroupId=@SecurityGroupId"
            Dim sqlPar1 As New SqlCeParameter("@SecurityGroupId", securityGroupId)
            Environment.ExecuteNonQuery(strSql, transactionScope, sqlPar1)
        End Sub
        Public Shared Sub AddGroupMember(ByVal securityGroupId As Integer, ByVal buddyId As Integer, ByVal transactionScope As TransactionScope)
            Dim strSql As String = "INSERT INTO SecurityGroupsMembers VALUES (@GroupId,@BuddyId)"
            Dim sqlPar1 As New SqlCeParameter("@GroupId", securityGroupId)
            Dim sqlPar2 As New SqlCeParameter("@BuddyId", buddyId)
            Environment.ExecuteNonQuery(strSql, transactionScope, sqlPar1, sqlPar2)
        End Sub
        Public Shared Sub RemoveAllGroupMembershipsForBuddy(ByVal buddyId As Integer, ByVal transactionScope As TransactionScope)
            Dim strSql As String = "DELETE FROM SecurityGroupsMembers WHERE BuddyId=@BuddyId"
            Dim sqlPar1 As New SqlCeParameter("@BuddyId", buddyId)
            Environment.ExecuteNonQuery(strSql, transactionScope, sqlPar1)
        End Sub
        Public Shared Function GetAllMemberIds(ByVal groupId As Integer) As Generic.List(Of Integer)
            Dim strSql As String = "select buddyid from securitygroupsmembers where securitygroupid=@SecurityGroupId"
            Dim sqlPar1 As New SqlCeParameter("@SecurityGroupId", groupId)
            Return Environment.SqlToIntegerList(strSql, sqlPar1)
        End Function

        Public Shared Sub Save(ByVal group As Entities.SecurityGroup)
            Using t As New TransactionScope(TransactionScope.TransactionMode.NullTransaction)
                Save(group, t)
                t.Complete()
            End Using
        End Sub

        Public Shared Sub Save(ByVal group As Entities.SecurityGroup, ByVal transactionScope As TransactionScope)
            Dim clsCmd As New SaveCommand(Of Integer)("SecurityGroups", "Id", group.Id, 0)
            clsCmd.AddParam("Name", group.Name, False)
            clsCmd.AddParam("Description", group.Description, False)
            clsCmd.AddParam("Type", group.Type, False)
            clsCmd.AddParam("SubType", group.SubType, False)

            group.Id = clsCmd.Save(transactionScope)
        End Sub


        Public Shared Sub Delete(ByVal id As Integer, ByVal transactionScope As TransactionScope)
            Dim strSql As String

            strSql = "DELETE FROM Shares WHERE Id = @ShareId"
            If Environment.ExecuteNonQuery(strSql, transactionScope, New SqlCeParameter("@ShareId", id)) <> 1 Then
                Throw New ApplicationException("No row was deleted!")
            End If
        End Sub

    End Class
End Namespace